# app.py
from flask import Flask, request, jsonify
from datetime import datetime
import pymysql
import os

app = Flask(__name__)


@app.route('/', methods=['GET'])
def hello():
    return "Hello, World!"


@app.route('/file_upload', methods=['POST'])
def upload_file():
    # 连接 MySQL 数据库
    connection = pymysql.connect(host='localhost',
                             user='root',
                             password='sq12194',
                             database='file_upload',
                             cursorclass=pymysql.cursors.DictCursor)
    # 获取上传的文件
    uploaded_file = request.files['file']
    team_name = request.form['team_name']
    phone_number = request.form['phone_number']
    
    if uploaded_file.filename == '':
        return jsonify({'error': 'No file selected for uploading'}), 400

    # 保存文件
    file_name = team_name + '-' + phone_number + '-' + datetime.now().strftime('%Y%m%d%H%M%S') + '.pdf'  # 假设上传的是 PDF 文件
    file_path = './files/' + file_name  # 替换为你想保存文件的路径

    # 写入或更新数据库记录
    try:
        with connection.cursor() as cursor:
            sql = "SELECT * FROM file_upload WHERE team_name=%s AND phone_number=%s"
            cursor.execute(sql, (team_name, phone_number))
            existing_record = cursor.fetchone()

            if existing_record:
                sql = "UPDATE file_upload SET file_path=%s WHERE team_name=%s AND phone_number=%s"
                cursor.execute(sql, (file_path, team_name, phone_number))
                # 保存新文件
                uploaded_file.save(file_path)
                # 删除旧文件
                old_file_path =  existing_record['file_path']
                os.remove(old_file_path)
            else:
                uploaded_file.save(file_path)
                sql = "INSERT INTO file_upload (phone_number, file_path, team_name) VALUES (%s, %s, %s)"
                cursor.execute(sql, (phone_number, file_path, team_name))
            # 记录日志
            sql = "INSERT INTO file_upload_log (phone_number, file_path, team_name) VALUES (%s, %s, %s)"
            cursor.execute(sql, (phone_number, file_path, team_name))

        connection.commit()
        return jsonify({'message': 'File uploaded successfully', 'file_path': file_path}), 200
    except Exception as e:
        print(e)
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.open:
            connection.close()


@app.route('/file_list', methods=['POST'])
def get_records():
    try:
        connection = pymysql.connect(host='localhost',
                             user='root',
                             password='sq12194',
                             database='file_upload',
                             cursorclass=pymysql.cursors.DictCursor)

        # 查询数据库获取记录
        with connection.cursor() as cursor:
            sql = "SELECT phone_number, file_path, team_name FROM file_upload"
            cursor.execute(sql)
            records = cursor.fetchall()
            return jsonify(records), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    finally:
        if connection.open:
            connection.close()


@app.route('/update_mark', methods=['POST'])
def update_mark():
    try:
        connection = pymysql.connect(host='localhost',
                             user='root',
                             password='sq12194',
                             database='file_upload',
                             cursorclass=pymysql.cursors.DictCursor)

        # 获取传入的参数
        expert_name = request.form['expert_name']
        expert_score = float(request.form['expert_score'])
        phone_number = request.form['phone_number']
        team_name = request.form['team_name']

        if expert_score > 100 or expert_score < 0:
            return jsonify({'message': 'Mark error'}), 200
        
        mark_field =  expert_name
        
        # 更新数据库记录
        with connection.cursor() as cursor:
            # 更新分数
            sql = f"UPDATE file_upload SET {expert_name}=%s WHERE phone_number=%s AND team_name=%s"
            cursor.execute(sql, (expert_score, phone_number, team_name))
            # 记录日志
            sql = "INSERT INTO mark_log (expert_name,phone_number, team_name,mark) VALUES (%s, %s, %s, %s)"
            cursor.execute(sql, (expert_name, phone_number, team_name,expert_score))
        connection.commit()

        return jsonify({'message': 'Mark updated successfully'}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500
    finally:
        connection.close()

if __name__ == '__main__':
    app.run(port=5000)
